﻿create PROCEDURE Sync.SP_wfStepInstances_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @wfStepInstances_Temp TABLE 
		(
			StepInstanceID UniqueIdentifier,
			[CreationDate] datetime,
				[ProcessInstanceID] uniqueidentifier,
				[StepID] uniqueidentifier,
				[ActionTypeID] uniqueidentifier,
				[PreviousStepInstanceID] uniqueidentifier,
				[CompletionDate] datetime,
				[UserID] uniqueidentifier,
				[Comments] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @wfStepInstances_Temp 
	   SELECT	Tbl.Col.value('StepInstanceID[1]','uniqueidentifier') StepInstanceID,
                Tbl.Col.value('CreationDate[1]', 'datetime') [CreationDate],
Tbl.Col.value('ProcessInstanceID[1]', 'uniqueidentifier') [ProcessInstanceID],
Tbl.Col.value('StepID[1]', 'uniqueidentifier') [StepID],
Tbl.Col.value('ActionTypeID[1]', 'uniqueidentifier') [ActionTypeID],
Tbl.Col.value('PreviousStepInstanceID[1]', 'uniqueidentifier') [PreviousStepInstanceID],
Tbl.Col.value('CompletionDate[1]', 'datetime') [CompletionDate],
Tbl.Col.value('UserID[1]', 'uniqueidentifier') [UserID],
Tbl.Col.value('Comments[1]', 'nvarchar(MAX)') [Comments],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/wfStepInstances') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[wfStepInstances] ([StepInstanceID],[CreationDate],[ProcessInstanceID],[StepID],[ActionTypeID],[PreviousStepInstanceID],[CompletionDate],[UserID],[Comments])
	SELECT t.[StepInstanceID],t.[CreationDate],t.[ProcessInstanceID],t.[StepID],t.[ActionTypeID],t.[PreviousStepInstanceID],t.[CompletionDate],t.[UserID],t.[Comments]
	FROM  @wfStepInstances_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.wfStepInstances_Tracking s
        WHERE t.StepInstanceID = s.StepInstanceID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[wfStepInstances]
	SET [wfStepInstances].[CreationDate] = t.[CreationDate],[wfStepInstances].[ProcessInstanceID] = t.[ProcessInstanceID],[wfStepInstances].[StepID] = t.[StepID],[wfStepInstances].[ActionTypeID] = t.[ActionTypeID],[wfStepInstances].[PreviousStepInstanceID] = t.[PreviousStepInstanceID],[wfStepInstances].[CompletionDate] = t.[CompletionDate],[wfStepInstances].[UserID] = t.[UserID],[wfStepInstances].[Comments] = t.[Comments] 
	FROM [dbo].[wfStepInstances] s JOIN @wfStepInstances_Temp t 
    ON t.StepInstanceID = s.StepInstanceID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.wfStepInstances_Tracking r
				  WHERE t.StepInstanceID = r.StepInstanceID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[wfStepInstances]
    FROM @wfStepInstances_Temp t JOIN [dbo].[wfStepInstances] s
    ON t.StepInstanceID = s.StepInstanceID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











